const express = require('express')
const Result = require('../models/Result')
const {
  orLike,
  orLimit,
  queryTotal,
  querySqlTotal,
  querySql,
  insert,
  deleteData,
  update
} = require('../db/index')

const router = express.Router()

/**
 * 查询用户的歌单列表
 */
router.get('/getUserSheet/:userId', async(req, res) => {
  let result = await querySql(`SELECT * FROM sheet WHERE userId = '${req.params.userId}'`)

  new Result(result, '获取用户列表成功').success(res)
})

/**
 * 新增歌单
 */
router.post('/addSheet', async(req, res) => {
  let result = await insert(`insert into sheet values(null, '${req.body.userId}', '${req.body.sheetName}', null, null, null)`, 'id')

  new Result(result, '新增成功').success(res)
})

// 修改歌单
router.post('/updateSheet', async(req, res) => {
  let temp = ''
  // sql拼接
  for(let key in req.body) {
    if(req.body[key] && !['id', 'userId'].includes(key)) {
      temp += `${key} = '${req.body[key]}',`
    }
  }
  temp = temp.substr(0, temp.length - 1)
  // 修改
  update(`UPDATE sheet SET ${temp} WHERE id = ${req.body.id}`)
  new Result(true, '保存成功').success(res)
})

// 删除歌单
router.delete('/deleteSheet/:sheetId', async(req, res) => {
  // 先删歌单表
  await deleteData(`delete from sheet where id = ${req.params.sheetId}`)
  // 再批量删歌单和歌曲的关联表
  await deleteData(`delete from sheet_song where sheetId = ${req.params.sheetId}`)
  new Result(true, '删除成功').success(res)
})

/**
 * 判断歌单是否有某一首歌
 * @param {*} sheetId
 * @param {*} songId
 */
let isSheetHasSong = (sheetId, songId) => {
  return new Promise(async (resolve, reject) => {
    let result = await querySql(`SELECT sheetId FROM sheet_song WHERE sheetId = '${sheetId}' AND songId = '${songId}'`)
    resolve(!!result.length)
  })
}

// 添加歌曲至歌单中
router.post('/addSong', async (req, res) => {
  // 批量增加先注释,后续会有批量增加的接口
  // let count = 0
  // req.body.list.forEach(async (item, index) => {
  //   let isHas = await isSheetHasSong(req.body.sheetId, item)
  //   if(!isHas) {
  //     count++
  //     // 追加
  //     await insert(`insert into sheet_song(sheetId, songId, isLike) values('${req.body.sheetId}', '${item}', null)`, req.body.sheetId)
  //   }
  //   if(index === req.body.list.length-1){
  //     new Result(count, '保存成功').success(res)
  //   }
  // })
  let isHas = await isSheetHasSong(req.body.sheetId, req.body.songId)
  if(isHas) {
    new Result(false, '保存失败,歌单中已存在该歌曲').fail(res)
    return
  }

  await insert(`insert into sheet_song(sheetId, songId, isLike) values('${req.body.sheetId}', '${req.body.songId}', null)`, req.body.sheetId)

  new Result(true, '保存成功').success(res)
})

/**
 * 将歌单的歌曲删除
 */
router.delete('/deleteSheetSong', async(req, res) => {
  await deleteData(`delete from sheet_song where sheetId = ${req.body.sheetId} and songId = ${req.body.songId}`)
  new Result(true, '删除成功').success(res)
})

/**
 * 查询歌单中具体的歌曲
 */
router.get('/getSheetSong/:sheetId', async(req, res) => {
  let result = await querySql(`SELECT
      DISTINCT t1.*,
      t2.singerName,
      t3.albumName,
      t4.isLike,
      t5.lyric
    FROM
      ( SELECT * FROM song WHERE id IN ( SELECT songId FROM sheet_song WHERE sheetId = ${req.params.sheetId} ) ) t1
      LEFT JOIN singer t2 ON t1.singer = t2.id
      LEFT JOIN album t3 ON t1.album = t3.id
      LEFT JOIN sheet_song t4 ON t1.id = t4.songId
      LEFT JOIN lyric t5 ON t1.id = t5.songId`)
  result.forEach(item => {
    if(item.lyric) item.lyric = item.lyric.replace(/\r/g, '\n')
  })
  new Result(result, '查询成功').success(res)
})

module.exports = router